package xy.jdbc.dao;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import xy.jdbc.entity.Book;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

/**
 * @Classname BookDao
 * @Description TODO
 * @Date 2023/7/25 17:56
 * @Created by wangxianlin
 */
@Repository
public class BookDao {
    // 新增
    private static final String INSERT_SQL = " insert into book (book_name,author,create_time) values (?,?,?) ";
    // 修改
    private static final String UPDATE_SQL = " update  book  set book_name = ? , author = ?  where id = ? ";
    // 删除
    private static final String DELETE_SQL_BY_ID = " delete from   book  where id = ? ";

    private static final String SELECT_ALL_SQL = " select * from book ";

    private static final String SELECT_BY_PAGE_SQL = " select * from book limit ?,? ";

    private static final String SELECT_BY_ID_SQL = " select * from book  where id = ? ";

    private static final String SELECT_COUNT_SQL = " select count(*) from book";

    @Resource
    JdbcTemplate jdbcTemplate;

    /**
     * 新增
     *
     * @param book
     */
    public int saveBook(Book book) {
        return this.jdbcTemplate.update(INSERT_SQL, book.getBookName(), book.getAuthor(), new Date());
    }

    /**
     * 修改
     *
     * @param book
     */
    public int updateBookById(Book book) {
        return this.jdbcTemplate.update(UPDATE_SQL, book.getBookName(), book.getAuthor(), book.getId());
    }

    /**
     * 删除
     *
     * @param id
     */
    public int deleteBookById(Integer id) {
        return this.jdbcTemplate.update(DELETE_SQL_BY_ID, id);
    }

    /**
     * 查询所有
     */
    public List<Book> queryAll() {
        return this.jdbcTemplate.query(SELECT_ALL_SQL, new RowMapper<Book>() {
            @Override
            public Book mapRow(ResultSet rs, int rowNum) throws SQLException {
                Book book = new Book();
                book.setId(rs.getInt("id"));
                book.setBookName(rs.getString("book_name"));
                book.setAuthor(rs.getString("author"));
                book.setCreateTime(rs.getTimestamp("create_time"));
                return book;
            }
        });
    }

    /**
     * 分页查询
     *
     * @return
     */
    public List<Book> queryByPage(int page, int size) {
        return this.jdbcTemplate.query(SELECT_BY_PAGE_SQL, new Object[]{page, size}, new RowMapper<Book>() {
            @Override
            public Book mapRow(ResultSet rs, int rowNum) throws SQLException {
                Book book = new Book();
                book.setId(rs.getInt("id"));
                book.setBookName(rs.getString("book_name"));
                book.setAuthor(rs.getString("author"));
                book.setCreateTime(rs.getTimestamp("create_time"));
                return book;
            }
        });
    }

    /**
     * 根据ID查询图书信息
     *
     * @param id
     */
    public Book queryBookById(Integer id) {
        return this.jdbcTemplate.queryForObject(SELECT_BY_ID_SQL, new Object[]{id}, Book.class);
    }

    /**
     * 查询所有图书数量
     */
    public Integer getBookCount() {
        return this.jdbcTemplate.queryForObject(SELECT_COUNT_SQL, Integer.class);
    }

}
